package com.xiaonei.DAO;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.util.DBConnection;
import com.xiaonei.model.*;

public  class SchoolsDAOImpl implements ISchoolsDAO{
	/**
     *求某个学校的学子�?     *根据学校名称查找学子�?     *@param  schoolName为学校名称；
     *@return 返回int型学校的id�?     */	
   public int getSchoolNum(String schoolName){
	   int schoolNum = 100;
	   Connection conn=DBConnection.getConnection();//得到数据库的连接�?		
	   ResultSet rs=null;//结果�?		
	   PreparedStatement pstmt=null;
		
		String sql="select school_num from school where name='"+schoolName+"'";
		try{
			pstmt=conn.prepareStatement(sql);
			rs=pstmt.executeQuery();
			while(rs.next()){
			schoolNum=rs.getInt(1);
			}	
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			DBConnection.free(rs, conn, pstmt);
		}
		return schoolNum;
   }
   /**
    *求某个学校的学子�?    *根据学校id查找学子�?    *@param  schoolNum为学校id�?    *@return 返回int型学校的学子总数�?    */	
   public int schoolStudentCount(int schoolNum){
	   int count=100;
	   Connection conn=DBConnection.getConnection();//得到数据库的连接�?		
	   ResultSet rs=null;//结果�?		
	   PreparedStatement pstmt=null;
		String sql="select account from user1 where school_num="+schoolNum+"";
		try{
			pstmt=conn.prepareStatement(sql);
			rs=pstmt.executeQuery();
			try{
				rs.last();//将数据库游标指向结果集的�?���?			
				}catch(SQLException e){
				e.printStackTrace();
			}
			try{
				count=rs.getRow();//得到结果集的总行数；
			}catch(SQLException e1){
				e1.printStackTrace();
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			DBConnection.free(rs, conn, pstmt);
		}
	   return count; 
   }
   
   
   /**
    *求关注某个学校的人数
    *根据学校id查找关注�?    *@param  schoolNum为学校id�?    *@return 返回int型关注该学校的人总数�?    */
   public int  userSchool(int schoolNum){
	   int count=100;
	   Connection conn=DBConnection.getConnection();//得到数据库的连接�?		
	   ResultSet rs=null;//结果�?		
	   PreparedStatement pstmt=null;
		String sql="select account from user_school where school_num="+schoolNum+"";
		try{
			pstmt=conn.prepareStatement(sql);
			rs=pstmt.executeQuery();
			try{
				rs.last();//将数据库游标指向结果集的�?���?			
				}catch(SQLException e){
				e.printStackTrace();
			}
			try{
				count=rs.getRow();//得到结果集的总行数；
			}catch(SQLException e1){
				e1.printStackTrace();
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			DBConnection.free(rs, conn, pstmt);
		}
	   return count; 
   }
   
   /**
    *显示学校�?��地区
    *根据学校id查找地址（省份）
    *@param  schoolNum为学校id�?    *@return 返回String型；
    */
   public String schoolAddress(int schoolNum){
	   String add=null;
	   Connection conn=DBConnection.getConnection();//得到数据库的连接�?		
	   ResultSet rs=null;//结果�?		
	   PreparedStatement pstmt=null;
		String sql="select province from school where school_num="+schoolNum+"";
		try{
			pstmt=conn.prepareStatement(sql);
			rs=pstmt.executeQuery();
			while(rs.next()){
			add=rs.getString(1);
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			DBConnection.free(rs, conn, pstmt);
		}
	   return add;
	}
   
   /**
    *求某个学校的name
    *根据学校名称找其name
    *@param  schoolNum为学校id�?    *@return 返回String型学校的name�?    */	
  public String getSchoolName(int schoolNum){
	  String name=null;
	  Connection conn=DBConnection.getConnection();//得到数据库的连接�?		
	  ResultSet rs=null;//结果�?		
	  PreparedStatement pstmt=null;
		
		String sql="select name from school where school_num="+schoolNum+"";
		try{
			pstmt=conn.prepareStatement(sql);
			rs=pstmt.executeQuery();
			while(rs.next()){
			name=rs.getString(1);
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			DBConnection.free(rs, conn, pstmt);
		}
		return name;
  }
  
  /**
   *求用户列�?   *根据学校id列出其学子账�?   *@param  schoolNum为学校id�?   *@return 返回ArrayList<String>�?   */	
  public ArrayList<String> getAccountList(int schoolNum){
	  ArrayList<String> list=new ArrayList<String>();
		Connection conn=DBConnection.getConnection();//得到数据库的连接�?		
		ResultSet rs=null;//结果�?		
		PreparedStatement pstmt=null;
		
		String sql="select account from user1,school where user1.school_num=school.school_num and school.school_num="+schoolNum+"";
		try{
			pstmt=conn.prepareStatement(sql);
			rs=pstmt.executeQuery();
			while(rs.next()){
				list.add(rs.getString(1));
			}	
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			DBConnection.free(rs, conn, pstmt);
		}
		return list;
  }
@Override
public boolean ifNotFoucs(String account, int schoolNum) {
	boolean a=false;
	User1DAO user1DAO=new User1DAO();
	if(schoolNum==user1DAO.getSchoolNumByAccount(account)){
		a=true;
	}else{
		Connection conn=DBConnection.getConnection();//得到数据库的连接�?		
		ResultSet rs=null;//结果�?		
		PreparedStatement pstmt=null;
		
		String sql="select * from user_school where account="+account+" and school_num="+schoolNum+"";
		try{
			pstmt=conn.prepareStatement(sql);
			rs=pstmt.executeQuery();
			while(rs.next()){
				a=true;
			}	
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			DBConnection.free(rs, conn, pstmt);
		}
	}
	return a;
}
@Override
public boolean toFoucs(String account, int schoolNum) {
		boolean a=false;
		Connection conn=DBConnection.getConnection();//得到数据库的连接�?	
		ResultSet rs=null;
		int crow=0;//结果�?		
		PreparedStatement pstmt=null;
		String time=null;
		String sql="insert into user_school values('"+account+"','"+schoolNum+"',time)";
		try{
			pstmt=conn.prepareStatement(sql);
		    crow=pstmt.executeUpdate(sql);
		    if(crow==0) a=false;
		    else if(crow==1) a=true;
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			DBConnection.free(rs, conn, pstmt);
		}
	return a;
}
@Override
public boolean delectFoucs(String account, int schoolNum) {
	boolean a=false;
	Connection conn=DBConnection.getConnection();//得到数据库的连接�?	
	ResultSet rs=null;
	int crow=0;//结果�?		
	PreparedStatement pstmt=null;
	String sql="delete from user_school where account="+account+" and school_num="+schoolNum+"";
	try{
		pstmt=conn.prepareStatement(sql);
	    crow=pstmt.executeUpdate(sql);
	    if(crow==0) a=false;
	    else if(crow==1) a=true;
	}catch(Exception e){
		e.printStackTrace();
	}finally{
		DBConnection.free(rs, conn, pstmt);
	}
return a;
}

public boolean  AddSchool(String name,String type,String introduction,String province,String city,String address){
	 boolean bool=false;
		Connection conn=DBConnection.getConnection();//得到数据库的连接�?		
		int crow;//结果�?		
		PreparedStatement pstmt=null;
		ResultSet rs=null;
		
		String sql="insert into school(name,type,introduction,province,city,address) values('"+name+"','"+type+"','"+introduction+"','"+province+"','"+city+"','"+address+"')";
		try{
			pstmt=conn.prepareStatement(sql);
		    crow=pstmt.executeUpdate(sql);
		    if(crow==0) bool=false;
		    else if(crow==1) bool=true;
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			DBConnection.free(rs, conn, pstmt);
		}
		return bool;
}
}
